﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Model;
namespace DAL
{
  public   class UserInfoDAL
    {
      /// <summary>
      /// 建立字符串链接
      /// </summary>
      SqlConnection con= new SqlConnection (" Data Source=PC201609040948;Initial Catalog=TianTianDai_Db;Persist Security Info=True;User ID=sa;Pwd=123456");
      /// <summary>
      /// 用户的详细显示
      /// </summary>
      /// <returns></returns>
      public List<UserInfo> UserInfo_Show ()
      {
          con.Open();
          string sql = "select * from UserInfo";
          SqlDataAdapter adp = new SqlDataAdapter(sql,con);
          DataTable table = new DataTable();
          adp.Fill(table);
          con.Close();
          List<UserInfo> list = new List<UserInfo>();
          foreach (DataRow item in table.Rows)
          {
              UserInfo user = new UserInfo();
              user.UserInfoId = Convert.ToInt32(item["UserInfoId"]);
              user.UserInvite = item["UserInvite"].ToString();
              user.UserName = item["UserName"].ToString();
              user.UserNum = item["UserNum"].ToString();
              user.UserPwd = item["UserPwd"].ToString();
              user.UserTel = item["UserTel"].ToString();
              user.RoleId = Convert.ToInt32(item["RoleId"]);

              list.Add(user);
          }
         return list;
         
         
      }
      /// <summary>
      /// 用户详情的添加
      /// </summary>
      /// <param name="m"></param>
      /// <returns></returns>
      public int UserInfo_Insert(UserInfo u)
      {
          con.Open();
          string sql = "inset into UserInfo values ('" + u.UserInvite + "','" + u.UserName + "','" + u.UserNum + "','" + u.UserPwd + "','" + u.UserTel + "','" + u.RoleId + "')";
          SqlCommand com = new SqlCommand(sql, con);
          return com.ExecuteNonQuery();
      }
      /// <summary>
      /// 用户名的删除
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public int UserInfo_Delete(int id)
      {
          con.Open();
          string sql = "delete UserInfo where UserInfoId='" + id + "'";
          SqlCommand com = new SqlCommand(sql, con);
          return com.ExecuteNonQuery();
      }
      /// <summary>
      /// 用户详情的显示或修改第一步
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public UserInfo UserInfo_details(int id)
      {
          con.Open();
          string sql = "select * from UserInfo Where UserInfoId='" + id + "'";
          SqlDataAdapter adp = new SqlDataAdapter(sql, con);
          DataTable table = new DataTable();
          adp.Fill(table);
          con.Close();
          List<UserInfo> list = new List<UserInfo>();
          foreach (DataRow item in table.Rows)
          {
              UserInfo user = new UserInfo();
              user.UserInfoId = Convert.ToInt32(item["UserInfoId"]);
              user.UserInvite =item["UserInvite"].ToString();
              user.UserName = item["UserName"].ToString();
              user.UserNum = item["UserNum"].ToString();
              user.UserPwd = item["UserPwd"].ToString();
              user.UserTel = item["UserTel"].ToString();
              user.RoleId = Convert.ToInt32(item["RoleId"]);

              list.Add(user);
          }
          return list.FirstOrDefault();
      }
      /// <summary>
      /// 用户表修改
      /// </summary>
      /// <param name="id"></param>
      /// <returns></returns>
      public int UserInfo_Edit(UserInfo u)
      {
          con.Open();
          string sql = "Update UserInfo set  UserName='" + u.UserName + "',UserInvite='" + u.UserInvite + "',UserNum='" + u.UserNum + "',UserPwd='" + u.UserPwd + "',UserTel='" + u.UserTel + "',RoleId='" + u.RoleId + "'";
          SqlCommand com = new SqlCommand(sql, con);
          return com.ExecuteNonQuery();
      }
      public List<UserInfo> UserInfo_Search(int RoleId)
      {
          con.Open();
          string sql = "select UserInfoId ,UserName,UserPwd,UserInvite,UserNum,UserTel,RoleId where RoleId like'" + RoleId + "'";
          SqlDataAdapter adp = new SqlDataAdapter(sql, con);
          DataTable table = new DataTable();
          adp.Fill(table);
          con.Close();
          List<UserInfo> list = new List<UserInfo>();
          foreach (DataRow item in table.Rows)
          {
              UserInfo user = new UserInfo();
              user.UserInfoId = Convert.ToInt32(item["UserInfoId"]);
              user.UserInvite = item["UserInvite"].ToString();
              user.UserName = item["UserName"].ToString();
              user.UserNum = item["UserNum"].ToString();
              user.UserPwd = item["UserPwd"].ToString();
              user.UserTel = item["UserTel"].ToString();
              user.RoleId = Convert.ToInt32(item["RoleId"]);

              list.Add(user);
          }
          return list;
      }

    }
}
